package org.codefinger.dao.impl.mssql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.codefinger.dao.impl.DataAccesser;
import org.codefinger.dao.impl.QueryBuilder;
import org.codefinger.dao.impl.TableInfo;
import org.codefinger.dao.impl.sql.UValueItem;
import org.codefinger.dao.util.Lang;
import org.codefinger.dao.util.jdbc.JDBCUtil;

public class SQLServerDataAccesser extends DataAccesser {

	public SQLServerDataAccesser(DataSource dataSource) {
		super(dataSource);
	}

	@Override
	protected QueryBuilder createQueryBuilder() {
		return new SQLServerQueryBuilder(this);
	}

	@Override
	protected TableInfo createTableInfo(String table) {
		table = table.toUpperCase();
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = dataSource.getConnection();

			statement = connection.prepareStatement("SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U' AND NAME = ?");
			statement.setString(1, table);
			resultSet = statement.executeQuery();
			if (resultSet.next()) {
				statement.close();
				resultSet.close();
			} else {
				throw Lang.makeThrow("Can not find the table named '%s'.", table);
			}

			statement = connection.prepareStatement("SELECT C.NAME,COLUMNPROPERTY(C.ID, C.NAME, 'ISIDENTITY') IS_IDENTITY FROM SYSINDEXES I JOIN SYSINDEXKEYS K ON I.ID = K.ID AND I.INDID = K.INDID JOIN SYSOBJECTS O ON I.ID = O.ID JOIN SYSCOLUMNS C ON I.ID=C.ID AND K.COLID = C.COLID WHERE O.XTYPE = 'U' AND EXISTS(SELECT 1 FROM SYSOBJECTS WHERE XTYPE = 'PK' AND NAME = I.NAME) AND O.NAME = ?");
			statement.setString(1, table);
			resultSet = statement.executeQuery();
			String primaryKeyName;
			if (resultSet.next()) {
				primaryKeyName = resultSet.getString(1).toUpperCase();
				if (!resultSet.getBoolean(2)) {
					throw Lang.makeThrow("The primary key '%s' of the table '%s' must be an identity column.", primaryKeyName, table);
				}
				statement.close();
				resultSet.close();
			} else {
				throw Lang.makeThrow("Can not find the primary key from the table '%s'.", table);
			}

			String primaryKey = null;
			List<String> columns = new ArrayList<String>();

			statement = connection.prepareStatement("SELECT * FROM SYSCOLUMNS WHERE ID = OBJECT_ID(?)");
			statement.setString(1, table);
			resultSet = statement.executeQuery();
			StringBuilder insertSQLBuilder = new StringBuilder("INSERT INTO ").append(table).append("(");
			boolean flag = false;
			while (resultSet.next()) {
				String columnName = resultSet.getString(1).toUpperCase();
				columns.add(columnName);
				if (columnName.equals(primaryKeyName)) {
					primaryKey = columnName;
					continue;
				}
				if (flag) {
					insertSQLBuilder.append(',').append(columnName);
				} else {
					insertSQLBuilder.append(columnName);
					flag = true;
				}
			}
			insertSQLBuilder.append(") VALUES (?");
			int size = columns.size();
			int count = size - 1;
			for (int i = 1; i < count; i++) {
				insertSQLBuilder.append(",?");
			}
			return new TableInfo(table, primaryKey, columns.toArray(new String[size]), insertSQLBuilder.append(')').toString());
		} catch (Throwable throwable) {
			throw Lang.wrapThrow(throwable, "Matched an error during find infomation from the table '%s'.", table);
		} finally {
			JDBCUtil.freeResource(connection, statement, resultSet);
		}
	}

	@Override
	protected QueryBuilder createWhereBuilder() {
		return new SQLServerQueryBuilder();
	}

	@Override
	protected QueryBuilder createUValueBuilder() {
		return new SQLServerQueryBuilder(new ArrayList<UValueItem>());
	}

	@Override
	protected QueryBuilder createIValueBuilder(String table) {
		return new SQLServerQueryBuilder(this, table);
	}

}
